/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package th.manager;


import com.google.inject.Inject;
import java.util.Collections;
import java.util.List;
import org.apache.commons.lang.StringUtils;
import org.hibernate.Query;
import org.hibernate.Session;
import th.dao.TaHaDAO;
import th.model.KodTransaksi;
import th.model.Pengguna;
import th.model.Taha;

/**
 *
 * @author fikri
 */
public class ReportManager {
    
    @Inject
    private TaHaDAO tahaDAO;
    
     @Inject
    protected com.google.inject.Provider<Session> sessionProvider;
    
    public List<Taha> searchStatTaha(Taha taha) {
        return Collections.EMPTY_LIST;
    }
    
    public List searchKelabTahaStat(Taha taha) {
        StringBuilder groupBy = new StringBuilder(" p.negeri.code ");
        StringBuilder whereStmt = new StringBuilder();
        StringBuilder sb = new StringBuilder("select distinct count(noAkaun), p.negeri.code ");
        
        if (taha != null) {
            if (!taha.getNegeri().getCode().equals("00")) {                
                if (whereStmt.length() > 0) whereStmt.append(",");
                whereStmt.append( " p.negeri.code = :negeri ");                
            }            
            
            if (taha.getJantina() != null) {
                 sb.append(",p.jantina.kod ");
                 if (!taha.getJantina().getKod().equals("00")) {
                     if (whereStmt.length() > 0) whereStmt.append(" AND ");
                    whereStmt.append( " p.jantina.kod = :jantina ");
                 }                 
                 if (groupBy.length() > 0) groupBy.append(",");
                 groupBy.append(" p.jantina.kod ");
            }
            sb.append(" FROM Taha p ");
            if (whereStmt.length() > 0) {
                sb.append(" WHERE ").append(whereStmt.toString());
            }
            
            if (groupBy.length() > 0) {
                sb.append(" GROUP BY ").append(groupBy.toString());
            }
        }
        
        Session session = sessionProvider.get();
        Query query = session.createQuery(sb.toString());
        if (taha != null && !taha.getNegeri().getCode().equals("00")) {
              query.setString("negeri", taha.getNegeri().getCode()); 
        }
        
        if (taha != null && taha.getJantina() != null && !taha.getJantina().getKod().equals("00")) {
              query.setString("jantina", taha.getJantina().getKod()); 
        }
        
        
         return query.list();            
    }
    
    
    public List searchTahaByNegeri(Taha taha) {
        StringBuilder sb = new StringBuilder("select count(noAkaun), p.negeri.code from Taha p ");
        if (taha != null && !taha.getNegeri().getCode().equals("00")) {
            sb.append("where p.negeri.code = :kod ");
        }
        sb.append("group by p.negeri.code");
        
        Session session = sessionProvider.get();
        Query query = session.createQuery(sb.toString());
        if (taha != null && !taha.getNegeri().getCode().equals("00")) {
              query.setString("kod", taha.getNegeri().getCode()); 
        }
        return query.list();        
    }
    
    public List searchTahaByJantina(Taha taha) {
        StringBuilder sb = new StringBuilder("select count(noAkaun), p.jantina.kod from Taha p ");
        if (taha != null && taha.getJantina() != null) {
            sb.append("where p.jantina.kod = :kod ");
        }
        sb.append("group by p.jantina.kod");
        
        Session session = sessionProvider.get();
        Query query = session.createQuery(sb.toString());
        if (taha != null && taha.getJantina() != null ) {
              query.setString("kod", taha.getJantina().getKod()); 
        }
        return query.list();        
    }
    
    public List searchByUrusan(KodTransaksi transaksi) {
        StringBuilder sb = new StringBuilder("SELECT count(l.idPgunaAppLog), date_format(l.tarikhMasuk, '%m/%Y') ")
                .append("from LogPenggunaApplikasi l ")                
                .append("WHERE l.transaksi.kod = :trans ")
                .append("GROUP by date_format(l.tarikhMasuk, '%m %Y')");       
        
        Session session = sessionProvider.get();
        Query query = session.createQuery(sb.toString()).setString("trans", transaksi.getKod());
        
        return query.list();
    }
    
}
